using Dapper;
using MySql.Data.MySqlClient;
using System;
using System.Data;
using System.Linq;
using Urs.Core;
using Urs.Core.Caching;
using Urs.Core.Data;
using Urs.Data.Domain.Agents;
using Urs.Data.Domain.Users;

namespace Urs.Services.Agents
{
    /// <summary>
    /// Agents service
    /// </summary>
    public partial class AgentService : IAgentService
    {
        #region Fields

        private readonly IRepository<AgentBonus> _agentBonusRepository;
        private readonly IRepository<AgentUser> _agentUserRepository;
        private readonly IRepository<User> _userRepository;
        private readonly ICacheManager _cacheManager;

        #endregion

        #region Ctor

        public AgentService(IRepository<AgentBonus> agentBonusRepository,
            IRepository<AgentUser> agentUserRepository,
            IRepository<User> userRepository,
            ICacheManager cacheManager)
        {
            _agentBonusRepository = agentBonusRepository;
            _agentUserRepository = agentUserRepository;
            _userRepository = userRepository;
            _cacheManager = cacheManager;
        }

        #endregion

        private MySqlConnection SqlConnection()
        {
            var dataSettings = DataSettingsManager.LoadSettings();
            var dataConnectionString = dataSettings.DataConnectionString;
            var connection = new MySqlConnection(dataConnectionString);
            connection.Open();
            return connection;
        }

        #region AgentBonus

        /// <summary>
        /// Deletes a bonus
        /// </summary>
        /// <param name="bonus">AgentBonus</param>
        public virtual void DeleteAgentBonus(AgentBonus bonus)
        {
            if (bonus == null)
                throw new ArgumentNullException("bonus");

            _agentBonusRepository.Delete(bonus);
        }

        /// <summary>
        /// Gets a bonus
        /// </summary>
        /// <param name="bonusId">The bonus identifier</param>
        /// <returns>AgentBonus</returns>
        public virtual AgentBonus GetAgentBonusById(int bonusId)
        {
            if (bonusId == 0)
                return null;

            return _agentBonusRepository.GetById(bonusId);
        }

        public virtual AgentBonus GetAgentBonusByOrderId(int orderId)
        {
            if (orderId == 0)
                return null;

            return _agentBonusRepository.Table.FirstOrDefault(q => q.OrderId == orderId);
        }

        public virtual IPagedList<AgentBonus> GetList(int affiliateId = 0, int parentAffiliateId = 0, DateTime? startTime = null, DateTime? endTime = null,
            int pageIndex = 0, int pageSize = int.MaxValue)
        {
            var query = _agentBonusRepository.Table;

            if (affiliateId > 0 && parentAffiliateId > 0)
                query = query.Where(q => q.AgentId == affiliateId || q.ParentAgentId == parentAffiliateId);
            else if (affiliateId > 0)
                query = query.Where(q => q.AgentId == affiliateId);
            else if (parentAffiliateId > 0)
                query = query.Where(q => q.ParentAgentId == parentAffiliateId);

            query = query.Where(q => q.IsValid == true);

            if (startTime.HasValue)
                query = query.Where(q => q.CreateTime >= startTime.Value);
            if (endTime.HasValue)
                query = query.Where(q => q.CreateTime <= endTime.Value);

            query = query.OrderByDescending(b => b.Id);
            var list = new PagedList<AgentBonus>(query, pageIndex, pageSize);
            return list;
        }

        public decimal GetFees(int affiliateId, bool? cash, bool? valid = null, DateTime? startTime = null, DateTime? endTime = null)
        {
            var query = _agentBonusRepository.Table;

            query = query.Where(q => q.AgentId == affiliateId);
            if (valid.HasValue)
                query = query.Where(q => q.IsValid == valid.Value);
            if (cash.HasValue)
                query = query.Where(q => q.Cash == cash.Value);
            if (startTime.HasValue)
                query = query.Where(q => q.CreateTime >= startTime.Value);
            if (endTime.HasValue)
                query = query.Where(q => q.CreateTime <= endTime.Value);

            return query.Sum(q => q.Fee);
        }

        public int GetCount(int agentId)
        {
            var query = _agentBonusRepository.Table;
            query = query.Where(q => q.AgentId == agentId);
            query = query.Where(q => q.IsValid == true);
            query = query.Where(q => q.Cash == false);
            var agentCount = query.Count();

            var parentQuery = _agentBonusRepository.Table;
            parentQuery = parentQuery.Where(q => q.ParentAgentId == agentId);
            parentQuery = parentQuery.Where(q => q.IsValid == true);
            parentQuery = parentQuery.Where(q => q.ParentCash == false);
            var parentCount = parentQuery.Count();

            return agentCount + parentCount;
        }

        public void ModifyCashForFees(int agentId)
        {
            var query = _agentBonusRepository.Table;

            query = query.Where(q => q.AgentId == agentId);
            query = query.Where(q => q.IsValid == true);
            query = query.Where(q => q.Cash == false);
            var list = query.ToList();
            foreach (var item in list)
            {
                item.Cash = true;
                _agentBonusRepository.Update(item);
            }

            var parentQuery = _agentBonusRepository.Table;
            parentQuery = parentQuery.Where(q => q.ParentAgentId == agentId);
            parentQuery = parentQuery.Where(q => q.IsValid == true);
            parentQuery = parentQuery.Where(q => q.ParentCash == false);
            var parentList = parentQuery.ToList();
            foreach (var item in parentList)
            {
                item.ParentCash = true;
                _agentBonusRepository.Update(item);
            }
        }

        public decimal GetParentFees(int affiliateId, bool? cash, bool? valid = null, DateTime? startTime = null, DateTime? endTime = null)
        {
            var query = _agentBonusRepository.Table;

            query = query.Where(q => q.ParentAgentId == affiliateId);
            if (valid.HasValue)
                query = query.Where(q => q.IsValid == valid.Value);
            if (cash.HasValue)
                query = query.Where(q => q.ParentCash == cash.Value);
            if (startTime.HasValue)
                query = query.Where(q => q.CreateTime >= startTime.Value);
            if (endTime.HasValue)
                query = query.Where(q => q.CreateTime <= endTime.Value);

            return query.Sum(q => q.ParentFee);
        }

        /// <summary>
        /// Inserts a bonus
        /// </summary>
        /// <param name="bonus">AgentBonus</param>
        public virtual void InsertAgentBonus(AgentBonus bonus)
        {
            if (bonus == null)
                throw new ArgumentNullException("bonus");

            _agentBonusRepository.Insert(bonus);
        }

        /// <summary>
        /// Updates the bonus
        /// </summary>
        /// <param name="bonus">AgentBonus</param>
        public virtual void UpdateAgentBonus(AgentBonus bonus)
        {
            if (bonus == null)
                throw new ArgumentNullException("bonus");

            _agentBonusRepository.Update(bonus);
        }

        #endregion


        #region AgentUser

        /// <summary>
        /// Deletes a user
        /// </summary>
        /// <param name="user">AgentUser</param>
        public virtual void DeleteAgentUser(AgentUser user)
        {
            if (user == null)
                throw new ArgumentNullException("user");

            _agentUserRepository.Delete(user);
        }

        public virtual AgentUser GetAgentUserByUserId(int userId)
        {
            if (userId == 0)
                return null;

            return _agentUserRepository.Table.FirstOrDefault(q => q.UserId == userId);
        }

        public virtual IPagedList<AgentUser> GetUserList(int pageIndex = 0, int pageSize = int.MaxValue)
        {
            var query = _agentUserRepository.Table;

            query = query.OrderByDescending(b => b.Id);
            var list = new PagedList<AgentUser>(query, pageIndex, pageSize);
            return list;
        }

        public virtual void InsertAgentUser(AgentUser user)
        {
            if (user == null)
                throw new ArgumentNullException("user");

            _agentUserRepository.Insert(user);
        }

        public virtual void UpdateAgentUser(AgentUser user)
        {
            if (user == null)
                throw new ArgumentNullException("user");

            _agentUserRepository.Update(user);
        }

        public virtual AgentSummary GetAgentSummary()
        {
            using (IDbConnection conn = SqlConnection())
            {
                var localsql = @"SELECT COUNT(*) AS val FROM userrolemapping ccm,user c WHERE ccm.userId=c.Id  AND c.Deleted=FALSE AND userRoleId=6;
SELECT COUNT(ccm.userId) as val FROM userrolemapping ccm,user c WHERE ccm.userId=c.Id AND  ccm.userRoleId=6 AND c.Deleted=FALSE AND DATE_FORMAT(c.CreateTime,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m');
SELECT COUNT(ccm.userId) as val FROM userrolemapping ccm,user c WHERE ccm.userId=c.Id AND  ccm.userRoleId=6 AND c.Deleted=FALSE AND c.`Approved`=TRUE;
SELECT COUNT(ccm.userId) as val FROM userrolemapping ccm,user c WHERE ccm.userId=c.Id AND  ccm.userRoleId=6 AND c.Deleted=FALSE AND c.`Approved`=FALSE;";
                var local = conn.QueryMultiple(localsql);
                if (!local.IsConsumed)
                {
                    var read1 = local.Read().FirstOrDefault();
                    var read2 = local.Read().FirstOrDefault();
                    var read3 = local.Read().FirstOrDefault();
                    var read4 = local.Read().FirstOrDefault();

                    return new AgentSummary()
                    {
                        NumberOfSum = (int)read1.val,
                        NumberOfMonth = (int)read2.val,
                        NumberOfPass = (int)read3.val,
                        NumberOfNoPass = (int)read4.val
                    };
                }
                else
                    return new AgentSummary();
            }
        }

        public virtual AgentUserSummary GetAgentUserSummary(int agentId)
        {
            using (IDbConnection conn = SqlConnection())
            {
                var localsql = @"SELECT COUNT(Id) AS val FROM user WHERE deleted=FALSE AND affiliateId=@AgentId;
                                SELECT COUNT(Id) AS val FROM `order` WHERE deleted=FALSE AND AffiliateId=@AgentId;
                                SELECT COUNT(Id) AS val FROM `order` WHERE deleted=FALSE AND OrderStatusId = 30 AND AffiliateId=@AgentId;
                                SELECT IF(SUM(OrderTotal) IS NULL,0, SUM(OrderTotal)) AS val FROM `order` WHERE deleted=FALSE AND AffiliateId=@AgentId;
                                SELECT IF(SUM(OrderTotal) IS NULL,0, SUM(OrderTotal)) AS val FROM `order` WHERE deleted=FALSE AND OrderStatusId = 30 AND AffiliateId=@AgentId;
                                SELECT IF((f1+f2) IS NULL,0, (f1 +f2)) AS val FROM  (SELECT SUM(Fee) AS f1 FROM AgentBonus WHERE AgentId=@AgentId) a1,(SELECT SUM(ParentFee) AS f2 FROM AgentBonus WHERE AgentId=@AgentId) AS a2;
                                SELECT IF((f1+f2) IS NULL,0, (f1 +f2)) AS val FROM  (SELECT SUM(Fee) AS f1 FROM AgentBonus WHERE IsValid=TRUE AND AgentId=@AgentId ) a1,(SELECT SUM(ParentFee) AS f2 FROM AgentBonus WHERE  IsValid=TRUE AND AgentId=@AgentId) AS a2;
                                SELECT IF((f1+f2) IS NULL,0, (f1 +f2)) AS val FROM  (SELECT SUM(Fee) AS f1 FROM AgentBonus WHERE IsValid=FALSE AND AgentId=@AgentId ) a1,(SELECT SUM(ParentFee) AS f2 FROM AgentBonus WHERE  IsValid=FALSE AND AgentId=@AgentId) AS a2;
                                SELECT IF((f1+f2) IS NULL,0, (f1 +f2)) AS val FROM  (SELECT SUM(Fee) AS f1 FROM AgentBonus WHERE IsValid=TRUE AND Cash=TRUE AND AgentId=@AgentId ) a1,(SELECT SUM(ParentFee) AS f2 FROM AgentBonus WHERE  IsValid=TRUE  AND ParentCash=TRUE AND AgentId=@AgentId) AS a2;
                                SELECT IF((f1+f2) IS NULL,0, (f1 +f2)) AS val FROM  (SELECT SUM(Fee) AS f1 FROM AgentBonus WHERE IsValid=TRUE AND Cash=FALSE AND AgentId=@AgentId ) a1,(SELECT SUM(ParentFee) AS f2 FROM AgentBonus WHERE  IsValid=TRUE  AND ParentCash=FALSE AND AgentId=@AgentId) AS a2;"
                                .Replace("@AgentId", agentId.ToString());

                var local = conn.QueryMultiple(localsql);
                if (!local.IsConsumed)
                {
                    var read1 = local.Read().FirstOrDefault();
                    var read2 = local.Read().FirstOrDefault();
                    var read3 = local.Read().FirstOrDefault();
                    var read4 = local.Read().FirstOrDefault();
                    var read5 = local.Read().FirstOrDefault();
                    var read6 = local.Read().FirstOrDefault();
                    var read7 = local.Read().FirstOrDefault();
                    var read8 = local.Read().FirstOrDefault();
                    var read9 = local.Read().FirstOrDefault();
                    var read10 = local.Read().FirstOrDefault();

                    return new AgentUserSummary()
                    {
                        NumberOfAgent = (int)read1.val,
                        NumberOfOrder = (int)read2.val,
                        NumberOfValidOrder = (int)read3.val,
                        AmountOfOrder = (int)read4.val,
                        AmountOfValidOrder = (decimal)read5.val,
                        TotalAmountOfFee = (decimal)read6.val,
                        AmountOfValidFee = (decimal)read7.val,
                        AmountOfNoValidFee = (decimal)read8.val,
                        CashFee = (decimal)read9.val,
                        NoCashFee = (decimal)read10.val
                    };
                }
                else
                    return new AgentUserSummary();
            }
        }
        #endregion
    }
}
